package com.yhp.dao.impl;

import com.yhp.bean.Grade;
import com.yhp.bean.Student;
import com.yhp.dao.StudentDao;
import com.yhp.utils.DBUtils;
import com.yhp.utils.State;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStu(int pageIndex,int pageSize,String stuname,String stuno,int sexNum) {
        List<Student> list = new ArrayList<>();
        List params = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer("select * from student s,grade g where s.gid=g.gradeid and state!=4 ");
            //按学生姓名查询
            if(stuname != null && stuname.length() > 0){
                sql.append(" and stuname like ? ");
                params.add("%"+stuname+"%");
            }
            //按学生学号查询
            if(stuno != null && stuno.length() > 0){
                sql.append(" and stuno = ?");
                params.add(stuno);
            }
            //按性别查询
            if (sexNum != -1){
                sql.append(" and sex = ? ");
                params.add(sexNum);
            }
            //按页码查询
            sql.append(" limit ?,? ");
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);
            resultSet = query(sql.toString(), params);

            while (resultSet.next()){
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuno(resultSet.getString("stuno"));
                student.setStuname(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegdate(resultSet.getDate("regdate"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public int total(String stuname,String stuno,int sexNum) {
        int count = 0;
        List params = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer("select count(*) from student where state!=4 ");
            //按学生姓名查询
            if(stuname != null && stuname.length() > 0){
                sql.append(" and stuname like ? ");
                params.add("%"+stuname+"%");
            }
            //按学生学号查询
            if(stuno != null && stuno.length() > 0){
                sql.append(" and stuno = ?");
                params.add(stuno);
            }
            //按性别查询
            if (sexNum != -1){
                sql.append(" and sex = ? ");
                params.add(sexNum);
            }


            resultSet = query(sql.toString(),params);
            while (resultSet.next()){
                count=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    @Override
    public Student getById(int id) {
        List params = new ArrayList();
        Student student = new Student();
        try {
            String sql = "select * from student s,grade g where s.gid=g.gradeid and state!=4 and stuid = ? ";
            params.add(id);
            resultSet = query(sql, params);
            while (resultSet.next()){
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                Grade grade = new Grade();
                grade.setGradeId(resultSet.getInt("gid"));
                grade.setGradeName(resultSet.getString("gradename"));
                student.setGrade(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int update(Student student) {
        List params = new ArrayList();
        int count = 0;
        try {
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?," +
                    "idnumber=?,politics=?,introduction=?,gid=? where stuid=? ";
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuid());
            count = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    @Override
    public int remove(int stuid) {
        List params = new ArrayList();
        int update = 0;
        try {
            String sql = "update student set state = ? where stuid = ? ";
            int state = 4;
            params.add(state);
            params.add(stuid);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int add(Student student) {
        List params = new ArrayList();
        int update = 0;
        try {
            String sql = " insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(State.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }

    @Override
    public String isrepeat(String stuno) {
        List params = new ArrayList();
        int i = 0;
        String isrepeat = null;
        try {
            String sql = "select * from student where stuno = ? ";
            params.add(stuno);
            resultSet = query(sql, params);
            while (resultSet.next()){
                i = resultSet.getInt(1);
                isrepeat = resultSet.getString("stuno");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return isrepeat;
    }
}
